﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using TS.Commons;
using TS.Entities;

namespace TS.DataAccess
{
    public class ScheduleDataAccess
    {
        /// <summary>
        /// Parse to Entity
        /// </summary>
        /// <param name="dataRecord"></param>
        /// <returns></returns>
        public static Schedule ParseToEntity(IDataRecord dataRecord)
        {
            return new Schedule
            {
                ScheduleId = dataRecord["ScheduleId"].ToStringOrDefault().ToNumeric(),
                GatewayId = dataRecord["GatewayId"].ToStringOrDefault().ToNumeric(),
                GateWayName = dataRecord["GateWayName"].ToStringOrDefault(),
                NumberBikeId = dataRecord["NumberBikeId"].ToStringOrDefault().ToNumeric(),
                NumberPlate = dataRecord["NumberPlate"].ToStringOrDefault(),
                DriverName = dataRecord["DriverName"].ToStringOrDefault(),
                DriverTel = dataRecord["DriverTel"].ToStringOrDefault(),
                Content = dataRecord["Content"].ToStringOrDefault(),
                StartDate = dataRecord["StartDate"].ToStringOrDefault().ToNumeric(),
                StartDateS = dataRecord["StartDateS"].ToStringOrDefault(),
                EndDate = dataRecord["EndDate"].ToStringOrDefault().ToNumeric(),
                EndDateS = dataRecord["EndDateS"].ToStringOrDefault(),
                OwnerId = dataRecord["OwnerId"].ToStringOrDefault().ToNumeric(),
                NumberCont = dataRecord["NumberCont"].ToStringOrDefault(),
                OwnerName = dataRecord["OwnerName"].ToStringOrDefault(),
                Status = dataRecord["Status"].ToStringOrDefault()
            };
        }

        /// <summary>
        /// Insert Schedule
        /// </summary>
        /// <param name="schedule"></param>
        /// <returns></returns>
        public int InsertSchedule(Schedule schedule)
        {
            int result = 0;
            if (schedule != null)
            {
                var parameters = new Collection<SqlParameter>
                                                          {
                                                              new SqlParameter("@GatewayId",schedule.GatewayId),
                                                              new SqlParameter("@OwnerId",schedule.OwnerId),
                                                              new SqlParameter("@NumberBikeId",schedule.NumberBikeId),
                                                              new SqlParameter("@Content",schedule.Content),
                                                              new SqlParameter("@StartDate",schedule.StartDate),
                                                              new SqlParameter("@EndDate",schedule.EndDate),
                                                              new SqlParameter("@NumberCont",schedule.NumberCont),
                                                              new SqlParameter("@Status",schedule.Status)
                                                          };

                using (IDataReader reader = SqlHelper.ExecuteReader(UISchedule.InsertScheduleQuery, parameters))
                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            result = Convert.ToInt32(reader["ScheduleId"], CultureInfo.CurrentCulture);
                        }
                        return result;
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// Get All Schedule
        /// </summary>
        /// <returns></returns>
        public Collection<Schedule> GetAllSchedule()
        {
            Collection<SqlParameter> collection = new Collection<SqlParameter>
            {
            };
            int totalRecord = 0;
            using (IDataReader reader = SqlHelper.ExecuteReader(UISchedule.SelectAllScheduleQuery, collection))
            {
                var result = new Collection<Schedule>();
                while (reader.Read())
                {
                    Schedule obj = ParseToEntity(reader);
                    obj.ShortContent = Utility.WriteString(obj.Content, 20);
                    result.Add(obj);
                }
                if (reader.NextResult())
                {
                    if (reader.Read())
                    {
                        totalRecord = reader["TotalRowCount"].ToNumeric();
                    }
                }
                return result;
            }

        }

        /// <summary>
        /// Read Schedule Pagging
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="strFilter"></param>
        /// <param name="strOrderBy"></param>
        /// <param name="day"> </param>
        /// <param name="ownerId"></param>
        /// <param name="status"> </param>
        /// <param name="totalRecord"></param>
        /// <param name="year"> </param>
        /// <param name="month"> </param>
        /// <param name="week"> </param>
        /// <returns></returns>
        public Collection<Schedule> ReadSchedulePaged(int pageSize, int pageIndex, string strFilter
                                                            , string strOrderBy
                                                            ,string year, string month, string week, string day,
                                                            string ownerId,
                                                             string status,
                                                            out int totalRecord)
        {
            var parameters = new Collection<SqlParameter>
                                                      {
                                                          new SqlParameter("@PageSize",pageSize),
                                                          new SqlParameter("@PageIndex",pageIndex),
                                                          new SqlParameter("@StringFilter",strFilter),
                                                          new SqlParameter("@OrderBy",strOrderBy),
                                                          new SqlParameter("@Year",year),
                                                          new SqlParameter("@Month",month),
                                                          new SqlParameter("@Week",week),
                                                          new SqlParameter("@Day",day),
                                                          new SqlParameter("@Status",status),
                                                          new SqlParameter("@OwnerId",ownerId)

                                                      };
            totalRecord = 0;

            //get result
            using (IDataReader reader = SqlHelper.ExecuteReader(UISchedule.ScheduleSelectPaged, parameters))
            {
                var result = new Collection<Schedule>();
                while (reader.Read())
                {
                    Schedule schedule = ParseToEntity(reader);
                    schedule.ShortContent = Utility.WriteString(schedule.Content, 20);

                    result.Add(schedule);
                }
                if (reader.NextResult())
                {
                    if (reader.Read())
                    {
                        totalRecord = reader["TotalRowCount"].ToNumeric();
                    }
                }
                return result;
            }
        }


        /// <summary>
        /// GetByScheduleId
        /// </summary>
        /// <param name="scheduleId"></param>
        /// <returns></returns>
        public Schedule GetByScheduleId(int scheduleId)
        {
            var collection = new Collection<SqlParameter>
                                 {
                                     new SqlParameter
                                         (
                                         "@ScheduleId",
                                         scheduleId
                                         )
                                 };

            using (IDataReader iDataReader = SqlHelper.ExecuteReader(UISchedule.ScheduleSelectById, collection))
            {
                if (iDataReader.Read())
                {
                    return ParseToEntity(iDataReader);
                }
            }
            return null;
        }

        /// <summary>
        /// update Schedule
        /// </summary>
        /// <param name="schedule"></param>
        /// <returns></returns>
        public int UpdateSchedule(Schedule schedule)
        {
            int result = 0;
            if (schedule != null)
            {
                var parameters = new Collection<SqlParameter>
                                     {
                                         new SqlParameter("@ScheduleId", schedule.ScheduleId),
                                         new SqlParameter("@GatewayId", schedule.GatewayId),
                                         new SqlParameter("@OwnerId", schedule.OwnerId),
                                         new SqlParameter("@NumberBikeId", schedule.NumberBikeId),
                                         new SqlParameter("@NumberCont", schedule.NumberCont),
                                         new SqlParameter("@Content", schedule.Content),
                                         new SqlParameter("@StartDate", schedule.StartDate),
                                         new SqlParameter("@Status", schedule.Status),
                                         new SqlParameter("@EndDate", schedule.EndDate)
                                     };

                using (IDataReader reader = SqlHelper.ExecuteReader(UISchedule.UpdateScheduleQuery, parameters))
                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            result = Convert.ToInt32(reader["ScheduleId"], CultureInfo.CurrentCulture);
                        }
                        return result;
                    }
                }

            }
            return result;
        }

        /// <summary>
        /// DeleteSchedule
        /// </summary>
        /// <param name="schedule"></param>
        /// <returns></returns>
        public int DeleteSchedule(Schedule schedule)
        {
            int result = 0;
            if (schedule != null)
            {
                var parameters = new Collection<SqlParameter>
                                     {
                                         new SqlParameter("@ScheduleId", schedule.ScheduleId)
                                     };

                using (IDataReader reader = SqlHelper.ExecuteReader(UISchedule.DelteScheduleQuery, parameters))
                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            result = Convert.ToInt32(reader["ScheduleId"], CultureInfo.CurrentCulture);
                        }
                        return result;
                    }
                }
            }

            return result;
        }
    }
}
